USE Lib
GO

---------------------------------------------------------------------------
-- BEGIN MAINTENANCE WRAPPER
----------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'fn_smart_replace') BEGIN
	DROP  FUNCTION  tsql.fn_smart_replace
	PRINT '<<< DROP FUNCTION tsql.fn_smart_replace - Completed with SUCCESS >>>'
END
GO

/*================================================================================================
Name:			tsql.fn_smart_replace
Type:			User defined function 
Owner:					
Description:	Replace repeated sequences chars with length between a min and a max 
Parameters:		@string = string to perforn replace on
				@search_for = substring to search for repetitions and replace
				@replace_with = replacement value
				@min_replace_len = min number of repetitions to replace 
				@max_replace_len = max number of repetions to replace 
Dependens on:  	none
Usage:			SELECT fn_smart_replace('qwertyyyyasdfggggg','g','',2,4)
--------------------------------------------------------------------------------------------------
Project:		http://sqllib.codeplex.com/
Help:			http://sqllib.codeplex.com/wikipage?title=fn_smart_replace&referringTitle=Documentation		
Version:		20120822
SrcCtrlApp:		SVN
SrcCtrlPath:	http://sqllib.codeplex.com/SourceControl/list/changesets
Note*:			CTRL + click on the link above to load the page
--------------------------------------------------------------------------------------------------
History:		05/03/2007 - Adrian E Dudau - Function created
History:		08/13/2012 - Adrian E Dudau - Renamed to fn_smart_replace from fn_replace_repetition
History:		08/22/2012 - Adrian E Dudau - Added references to project, SVN and Help
================================================================================================*/
CREATE FUNCTION tsql.fn_smart_replace
	(  @string NVARCHAR(4000)
	 , @search_for NVARCHAR(4000)
     , @replace_with NVARCHAR(4000)
	 , @min_replace_len INT = 1
	 , @max_replace_len INT = 4000
	)
RETURNS NVARCHAR(4000)
WITH EXECUTE AS CALLER
AS

BEGIN 
	DECLARE @n AS INT

	IF @min_replace_len < 0 
		SET @min_replace_len = 0

	IF @max_replace_len < @min_replace_len 
		SET @max_replace_len = @min_replace_len 

	SET @n = @max_replace_len

	WHILE @n >= @min_replace_len BEGIN 
		SET @string = REPLACE(@string,REPLICATE(@search_for,@n),@replace_with)
		SET @String = REPLACE(@string,REPLICATE(@replace_with,2),@replace_with)
		SET @n = @n - 1
	END

	RETURN @string
END

GO

---------------------------------------------------------------------------
-- BEGIN MAINTENANCE WRAPPER
-- Verify completion
----------------------------
IF EXISTS(SELECT * FROM sysobjects WHERE type = 'FN' AND name = 'fn_smart_replace') BEGIN 
	PRINT '<<< CREATE FUNCTION tsql.fn_smart_replace - Completed with SUCCESS >>>'
END
ELSE BEGIN 
	PRINT '<<< CREATE FUNCTION tsql.fn_smart_replace - Completed with ERROR >>>'
END

GO

----------------------------
-- END MAINTENANCE WRAPPER
---------------------------------------------------------------------------
